
from quant_researcher.quant.project_tool.time_tool import get_today, get_yesterday
import pandas as pd
import os
from get_currency_history_price import get_currency_historical_price, get_currency_latest_price
from fetch_okex_usdtusd_preminum import fetch_okex_usdtusd_premium
from fetch_okex_usdtcny_premium import fetch_okex_usdtcny_premium
from get_btc126_usdt_premium import get_btc126_usdt_premium
import numpy as np
from quant_researcher.quant.datasource_fetch.crypto_api.glassnode import get_prices


def usdt_premium_data_update():
    """
    # 获取usdt otc 兑 人民币价格
    # 数据需要网站爬取，部分数据（5m频）因为网站上不会保留那么久，因此需要每天爬取

    :return:
    """
    file_path = os.path.join("G:/", f'usdt_premium')
    os.makedirs(file_path, exist_ok=True)

    today = get_today(marker='with_n_dash')

    # 获取更新全历史离岸人民币汇率数据, 注意获取的时间为北京时间
    file_name = os.path.join(file_path, f'USDCNH_exchange_rate.csv')
    if os.path.exists(file_name):
        historical_usdcnh = pd.read_csv(file_name)
        if max(historical_usdcnh['date']) < today:
            usdcnh = get_currency_historical_price(symbol='USDCNH')
            all_usdcnh = pd.concat([historical_usdcnh, usdcnh], axis=0)
            all_usdcnh.drop_duplicates(subset=['date'], keep='last')
            all_usdcnh.to_csv(file_name, index=False)
        else:
            pass
    else:
        all_usdcnh = get_currency_historical_price(symbol='USDCNH')
        all_usdcnh.to_csv(file_name, index=False)

    usdcnh_latest_price = get_currency_latest_price(symbol='USDCNH')

    # 从okx获取USDT溢价数据， 注意这里获取的是USDT-USD数据
    # for frequency in ['5m', '1h', '1d']:
    #     okx_usdt_premium = fetch_okex_usdtusd_premium(frequnecy=frequency)
    #     file_name = os.path.join(file_path, f'okx_usdtusd_premium_{frequency}.xlsx')
    #     if os.path.exists(file_name):
    #         historical_data = pd.read_excel(file_name)
    #         if max(historical_data['datetime']) < max(okx_usdt_premium['datetime']):
    #             all_df = pd.concat([historical_data, okx_usdt_premium], axis=0, ignore_index=True)
    #             all_df.drop_duplicates(subset=['datetime'], keep='first', inplace=True)
    #             all_df.to_excel(f'{file_name}', index=False)
    #         else:
    #             pass
    #     else:
    #         okx_usdt_premium.to_excel(f'{file_name}', index=False)
    from filelock import FileLock
    for frequency in ['5m', '1h', '1d']:
        # 1. 从okex拉取最新的数据
        okx_usdt_premium = fetch_okex_usdtusd_premium(frequnecy=frequency)
        # 2. 确定保存的 Excel 文件名和对应的lock文件
        file_name = os.path.join(file_path, f'okx_usdtusd_premium_{frequency}.xlsx')
        lock_file = file_name + ".lock"  # 用同路径不同后缀做锁文件
        # 3. 用 FileLock 做并发保护
        with FileLock(lock_file):
            # 如果存在旧Excel，先读出来和新数据合并
            if os.path.exists(file_name):
                historical_data = pd.read_excel(file_name)
                if max(historical_data['datetime']) < max(okx_usdt_premium['datetime']):
                    all_df = pd.concat([historical_data, okx_usdt_premium], axis=0, ignore_index=True)
                    all_df.drop_duplicates(subset=['datetime'], keep='first', inplace=True)
                    all_df.to_excel(f'{file_name}', index=False)
                else:
                    pass
            else:
                okx_usdt_premium.to_excel(file_name, index=False)
    # 从okx 获取usdt对cny的报价信息; 每1小时获取一次
    ticker_file_name = os.path.join(file_path, f'okx_usdtcny_premium_ticker.xlsx')
    orderbook_file_name = os.path.join(file_path, f'okx_usdtcny_premium_orderbook.csv')

    if os.path.exists(ticker_file_name) and os.path.exists(orderbook_file_name):
        ticker_historical_data = pd.read_excel(ticker_file_name)
        orderbook_historical_data = pd.read_csv(orderbook_file_name)

        df_ticker, df_orderbook = fetch_okex_usdtcny_premium()
        df_ticker['usdcnh'] = usdcnh_latest_price
        df_ticker['premium'] = (df_ticker['usdtcny'] - usdcnh_latest_price) / usdcnh_latest_price
        df_orderbook['usdcnh'] = usdcnh_latest_price

        if max(ticker_historical_data['datetime']) < max(df_ticker['datetime']):
            all_df = pd.concat([ticker_historical_data, df_ticker], axis=0, ignore_index=True)
            all_df.drop_duplicates(subset=['datetime'], keep='first', inplace=True)
            all_df.to_excel(f'{ticker_file_name}', index=False)
        else:
            pass

        if max(orderbook_historical_data['datetime']) < max(df_orderbook['datetime']):
            all_df = pd.concat([orderbook_historical_data, df_orderbook], axis=0, ignore_index=True)
            all_df.drop_duplicates(subset=['datetime', 'id'], keep='first', inplace=True)
            all_df.to_csv(f'{orderbook_file_name}', index=False)
        else:
            pass

    else:
        df_ticker, df_orderbook = fetch_okex_usdtcny_premium()
        df_ticker['usdcnh'] = usdcnh_latest_price
        df_ticker['premium'] = (df_ticker['usdtcny'] - usdcnh_latest_price) / usdcnh_latest_price
        df_orderbook['usdcnh'] = usdcnh_latest_price
        df_ticker.to_excel(f'{ticker_file_name}', index=False)
        df_orderbook.to_csv(f'{orderbook_file_name}', index=False)

    # # 从非小号获取USDT溢价数据
    # for period in ['24h', '7day', '30day']:
    #     fxh_usdt_premium = get_feixiaohao_usdt_premium(period=period)
    #     file_name = os.path.join(file_path, f'fxh_usdtcny_premium_{period}.xlsx')
    #     if os.path.exists(file_name):
    #         historical_data = pd.read_excel(file_name)
    #         if max(historical_data['datetime']) < max(fxh_usdt_premium['datetime']):
    #             all_df = pd.concat([historical_data, fxh_usdt_premium], axis=0, ignore_index=True)
    #             all_df.drop_duplicates(subset=['datetime'], keep='first', inplace=True)
    #             all_df.to_excel(f'{file_name}', index=False)
    #         else:
    #             pass
    #     else:
    #         fxh_usdt_premium.to_excel(f'{file_name}', index=False)

    file_name = os.path.join(file_path, f'btc126_usdtcny_premium.xlsx')
    if os.path.exists(file_name):
        historical_data = pd.read_excel(file_name)
        if max(historical_data['atime']) < get_yesterday(marker='with_n_dash'):
            btc126_usdt_premium = get_btc126_usdt_premium()
            all_df = pd.concat([historical_data, btc126_usdt_premium], axis=0, ignore_index=True)
            all_df.drop_duplicates(subset=['datetime'], keep='first', inplace=True)
            all_df.to_excel(f'{file_name}', index=False)
        else:
            pass
    else:
        btc126_usdt_premium = get_btc126_usdt_premium()
        btc126_usdt_premium.to_excel(f'{file_name}', index=False)

    # # 读取okx给的USDTCNY汇率数据
    # file_name = os.path.join(file_path, f'okx USDT对人民币汇率.xlsx')
    # okx_usdt_premium_offical = pd.read_excel(file_name)
    # okx_usdt_premium_offical['datetime'] = pd.to_datetime(okx_usdt_premium_offical['create_time']).dt.strftime('%Y-%m-%d %H:%M:%S')
    # okx_usdt_premium_offical['date'] = pd.to_datetime(okx_usdt_premium_offical['create_time']).dt.strftime('%Y-%m-%d')
    # all_date_list = [x.strftime("%Y-%m-%d") for x in pd.date_range(min(okx_usdt_premium_offical['date']), max(okx_usdt_premium_offical['date']))]
    # missing_date_list = [x for x in all_date_list if x + ' 23:59:59' not in list(okx_usdt_premium_offical['datetime'])]
    # all_missing_datetime_list = [x + ' 23:59:59' for x in missing_date_list]
    # all_datetime_list = all_missing_datetime_list + list(okx_usdt_premium_offical['datetime'])
    # okx_usdt_premium_offical.set_index('datetime', inplace=True)
    # okx_usdt_premium_offical = okx_usdt_premium_offical.reindex(all_datetime_list)
    # okx_usdt_premium_offical.sort_index(inplace=True)
    # okx_usdt_premium_offical.rename(columns={'rate_parities': 'usdtcny'}, inplace=True)
    # okx_usdt_premium_offical = okx_usdt_premium_offical[['usdtcny']]
    # okx_usdt_premium_offical.ffill(inplace=True)
    # okx_usdt_premium_offical['date'] = okx_usdt_premium_offical.index.str[:10]
    #
    # prices_df = get_prices(ohlc=False, asset='BTC', start_date='2014-01-01', end_date=None, interval='1h')
    # prices_df = prices_df[['close']]
    # prices_df['log_price'] = np.log10(prices_df['close'])
    #
    # file_name = os.path.join(file_path, f'USDCNH_exchange_rate.xlsx')
    # all_usdcnh = pd.read_excel(file_name)
    # all_usdcnh.drop_duplicates(subset='date', inplace=True)
    # all_usdcnh = all_usdcnh[(all_usdcnh['date'] >= min(okx_usdt_premium_offical['date'])) & (all_usdcnh['date'] <= max(okx_usdt_premium_offical['date']))]
    # all_usdcnh['datetime'] = [x + ' 23:59:59' for x in all_usdcnh['date']]
    # all_usdcnh.set_index('datetime', inplace=True)
    #
    # # 合并数据
    # okx_usdt_premium_offical = all_usdcnh.merge(okx_usdt_premium_offical, left_index=True, right_index=True, how='left')
    # okx_usdt_premium_offical['premium'] = (okx_usdt_premium_offical['usdtcny'] - okx_usdt_premium_offical['close']) / okx_usdt_premium_offical['close']
    # file_name = os.path.join(file_path, f'okx_usdtcny_premium_ticker.xlsx')
    # okx_usdtcny_premium_ticker = pd.read_excel(file_name)
    # okx_usdtcny_premium_ticker.set_index('datetime', inplace=True)
    # all_df = pd.concat([okx_usdt_premium_offical, okx_usdtcny_premium_ticker], axis=0)
    # all_df = all_df[['premium']]
    #
    # all_datetime_list = list(prices_df.index) + list(all_df.index)
    # prices_df = prices_df.reindex(all_datetime_list)
    # prices_df.sort_index(inplace=True)
    # prices_df.ffill(inplace=True)
    # all_df = all_df.merge(prices_df, left_index=True, right_index=True, how='left')
    # all_df.loc[all_df['premium'] > 0.1, 'premium'] = 0
    # all_df.loc[all_df['premium'] < -0.1, 'premium'] = 0
    # file_name = os.path.join(file_path, f'okx USDT对人民币汇率_汇总.xlsx')
    # all_df.to_excel(file_name)

    # 获取BTC_ohlcv数据, 日频数据
    file_name = os.path.join("G:/", f'BTC_history_ohlcvm')
    ohlcv_data = pd.read_excel(f'{file_name}.xlsx', index_col='end_date')
    prices_df = ohlcv_data[['close']]
    prices_df['log_price'] = np.log10(prices_df['close'])

    file_name = os.path.join(file_path, f'btc126_usdtcny_premium.xlsx')
    btc126_usdt_premium = pd.read_excel(file_name)
    all_df = btc126_usdt_premium.merge(prices_df, left_on='atime', right_index=True)
    file_name = os.path.join(file_path, f'btc126_usdtcny_premium_log_prices.xlsx')
    all_df.to_excel(file_name, index=False)

    # 获取小时频价格数据
    prices_df = get_prices(ohlc=False, asset='BTC', start_date='2022-09-20', end_date=None, interval='1h')
    prices_df = prices_df[['close']]
    prices_df['log_price'] = np.log10(prices_df['close'])

    ticker_file_name = os.path.join(file_path, f'okx_usdtcny_premium_ticker.xlsx')
    okx_usdtcny_premium_ticker = pd.read_excel(ticker_file_name)
    okx_usdtcny_premium_ticker.set_index('datetime', inplace=True)
    all_datetime_list = list(prices_df.index) + list(okx_usdtcny_premium_ticker.index)
    prices_df = prices_df.reindex(all_datetime_list)
    prices_df.sort_index(inplace=True)
    prices_df.ffill(inplace=True)
    prices_df = prices_df.reindex(okx_usdtcny_premium_ticker.index)
    all_df = okx_usdtcny_premium_ticker.merge(prices_df, left_index=True, right_index=True)
    file_name = os.path.join(file_path, f'okx_usdtcny_premium_ticker_log_prices.xlsx')
    all_df.to_excel(file_name)


if __name__ == "__main__":
    usdt_premium_data_update()